
#Importing libraries
from sqlalchemy import create_engine
import pandas as pd

#Creating an SQLAlchemy engine
engine = create_engine('mssql+pyodbc://CLARK\SQLEXPRESS/Portfolio?trusted_connection=yes&driver=ODBC+Driver+17+for+SQL+Server')


#Executing SQL query and fetching the results into a DataFrame
query1 = 'SELECT * FROM dbo.Housing_Data ORDER BY ParcelID;'
df = pd.read_sql(query1, engine)

#Querying the first five rows of the DataFrame
df.head()


#Querying rows with NULL values of PropertyAddress
query2 = '''
SELECT *
FROM Portfolio.dbo.Housing_Data
WHERE PropertyAddress IS NULL
ORDER BY ParcelID;
'''

df2 = pd.read_sql(query2, engine)
df2


#Querying rows with NULL values of PropertyAddress that have a matching ParcelID and assigning the same PropertyAddress
query3 = '''
SELECT a.ParcelID, a.PropertyAddress, b.ParcelID, b.PropertyAddress, ISNULL(a.PropertyAddress,b.PropertyAddress) AS PropertyAddress
FROM Portfolio.dbo.Housing_Data a
JOIN Portfolio.dbo.Housing_Data b
    ON a.ParcelID = b.ParcelID
    AND a.[UniqueID ] <> b.[UniqueID ]
WHERE a.PropertyAddress IS NULL;
'''

df3 = pd.read_sql(query3, engine)
df3


#Updating the PropertyAddress column
update1 = '''
UPDATE a
SET PropertyAddress = ISNULL(a.PropertyAddress,b.PropertyAddress)
FROM Portfolio.dbo.Housing_Data a
JOIN Portfolio.dbo.Housing_Data b
    ON a.ParcelID = b.ParcelID
    AND a.[UniqueID ] <> b.[UniqueID ]
WHERE a.PropertyAddress IS NULL;
'''

with engine.begin() as connection:
    connection.execute(update1)


#Checking if there are still NULL values of PropertyAddress
df4 = pd.read_sql(query2, engine)
df4


#Querying PropertyAddress column
query5 = '''
SELECT PropertyAddress
FROM Portfolio.dbo.Housing_Data
ORDER BY ParcelID;
'''

df5 = pd.read_sql(query5, engine)
df5.head()


#Using SUBSTRING to extract the street address and city from the PropertyAddress column
query6 = '''
SELECT
SUBSTRING(PropertyAddress, 1, CHARINDEX(',', PropertyAddress) -1) AS PropertyAddressStreet,
SUBSTRING(PropertyAddress, CHARINDEX(',', PropertyAddress) +1, LEN(PropertyAddress)) AS PropertyAddressCity
FROM Portfolio.dbo.Housing_Data
ORDER BY ParcelID;
'''

df6 = pd.read_sql(query6, engine)
df6.head()


#Adding a new column PropertyAddressStreet
update2 = '''
ALTER TABLE Housing_Data
ADD PropertyAddressStreet NVARCHAR(255);
'''

with engine.begin() as connection:
    connection.execute(update2)


#Updating the PropertyAddressStreet column using SUBSTRING to extract the street address from the PropertyAddress column
update3 = '''
UPDATE Housing_Data
SET PropertyAddressStreet = SUBSTRING(PropertyAddress, 1, CHARINDEX(',', PropertyAddress) -1);
'''

with engine.begin() as connection:
    connection.execute(update3)


#Querying the first five rows of the DataFrame
df = pd.read_sql(query1, engine)
df.head()


#Adding a new column PropertyAddressCity
update4 = '''
ALTER TABLE Housing_Data
ADD PropertyAddressCity NVARCHAR(255);
'''

with engine.begin() as connection:
    connection.execute(update4)


#Updating the PropertyAddressCity column using SUBSTRING to extract the city from the PropertyAddress column
update5 = '''
UPDATE Housing_Data
SET PropertyAddressCity = SUBSTRING(PropertyAddress, CHARINDEX(',', PropertyAddress) + 1 , LEN(PropertyAddress));
'''

with engine.begin() as connection:
    connection.execute(update5)


#Querying the first five rows of the DataFrame
df = pd.read_sql(query1, engine)
df.head()


#Querying OwnerAddress column
query7 = '''
SELECT OwnerAddress
FROM Portfolio.dbo.Housing_Data
ORDER BY ParcelID;
'''

df7 = pd.read_sql(query7, engine)
df7.head()


#Using PARSENAME to extract the street address, city, and state from the OwnerAddress column
query8 = '''
SELECT
PARSENAME(REPLACE(OwnerAddress, ',', '.') , 3) AS OwnerAddressStreet,
PARSENAME(REPLACE(OwnerAddress, ',', '.') , 2) AS OwnerAddressCity,
PARSENAME(REPLACE(OwnerAddress, ',', '.') , 1) AS OwnerAddressState
FROM Portfolio.dbo.Housing_Data
ORDER BY ParcelID;
'''

df8 = pd.read_sql(query8, engine)
df8.head()


#Adding a new column OwnerAddressStreet
update6 = '''
ALTER TABLE Housing_Data
ADD OwnerAddressStreet NVARCHAR(255);
'''

with engine.begin() as connection:
    connection.execute(update6)


#Updating the OwnerAddressStreet column using PARSENAME to extract the street address from the OwnerAddress column
update7 = '''
UPDATE Housing_Data
SET OwnerAddressStreet = PARSENAME(REPLACE(OwnerAddress, ',', '.') , 3);
'''

with engine.begin() as connection:
    connection.execute(update7)


#Querying the first five rows of the DataFrame
df = pd.read_sql(query1, engine)
df.head()


#Adding a new column OwnerAddressCity
update8 = '''
ALTER TABLE Housing_Data
ADD OwnerAddressCity NVARCHAR(255);
'''

with engine.begin() as connection:
    connection.execute(update8)


#Updating the OwnerAddressCity column using PARSENAME to extract the city from the OwnerAddress column
update9 = '''
UPDATE Housing_Data
SET OwnerAddressCity = PARSENAME(REPLACE(OwnerAddress, ',', '.') , 2);
'''

with engine.begin() as connection:
    connection.execute(update9)


#Querying the first five rows of the DataFrame
df = pd.read_sql(query1, engine)
df.head()


#Adding a new column OwnerAddressState
update10 = '''
ALTER TABLE Housing_Data
ADD OwnerAddressState NVARCHAR(255);
'''

with engine.begin() as connection:
    connection.execute(update10)


#Updating the OwnerAddressState column using PARSENAME to extract the state from the OwnerAddress column
update11 = '''
UPDATE Housing_Data
SET OwnerAddressState = PARSENAME(REPLACE(OwnerAddress, ',', '.') , 1);
'''

with engine.begin() as connection:
    connection.execute(update11)


#Querying the first five rows of the DataFrame
df = pd.read_sql(query1, engine)
df.head()


#SoldAsVacant column has inconsistent formatting
#There are 'Yes' and 'No' as well as 'Y' and 'N' values
query9 = '''
SELECT DISTINCT SoldAsVacant, COUNT(SoldAsVacant) AS Count_SoldAsVacant
FROM Portfolio.dbo.Housing_Data
GROUP BY SoldAsVacant
ORDER BY 2 DESC;
'''

df9 = pd.read_sql(query9, engine)
df9


#There are more 'Yes' and 'No' values than 'Y' and 'N' values respectively, hence, we will replace 'Y' and 'N'
query10 = '''
SELECT SoldAsVacant,
    CASE
        WHEN SoldAsVacant = 'Y' THEN 'Yes'
        WHEN SoldAsVacant = 'N' THEN 'No'
        ELSE SoldAsVacant
    END
FROM Portfolio.dbo.Housing_Data
WHERE SoldAsVacant IN ('Y', 'N');
'''

df10 = pd.read_sql(query10, engine)
df10


#Updating the SoldAsVacant column
update12 = '''
UPDATE Housing_Data
SET SoldAsVacant = CASE
        WHEN SoldAsVacant = 'Y' THEN 'Yes'
        WHEN SoldAsVacant = 'N' THEN 'No'
        ELSE SoldAsVacant
    END;
'''

with engine.begin() as connection:
    connection.execute(update12)


#Querying SoldAsVacant column value count again
df9 = pd.read_sql(query9, engine)
df9


#Querying duplicate rows using common table expression (CTE)
update13 = '''
WITH Duplicates AS (
    SELECT *,
        ROW_NUMBER() OVER (
        PARTITION BY ParcelID,
                     PropertyAddress,
                     SalePrice,
                     SaleDate,
                     LegalReference
        ORDER BY UniqueID
        ) rownum
    FROM Portfolio.dbo.Housing_Data
)
DELETE
FROM Duplicates
WHERE rownum > 1;
'''

with engine.begin() as connection:
    connection.execute(update13)


#Querying the first five rows of the DataFrame
df = pd.read_sql(query1, engine)
df.head()

	UniqueID	ParcelID	LandUse	PropertyAddress	SaleDate	SalePrice	LegalReference	SoldAsVacant	OwnerName	OwnerAddress	Acreage	TaxDistrict	LandValue	BuildingValue	TotalValue	YearBuilt	Bedrooms	FullBath	HalfBath
0	2045.0	007 00 0 125.00	SINGLE FAMILY	1808 FOX CHASE DR, GOODLETTSVILLE	2013-04-09	240000.0	20130412-0036474	No	FRAZIER, CYRENTHA LYNETTE	1808 FOX CHASE DR, GOODLETTSVILLE, TN	2.3	GENERAL SERVICES DISTRICT	50000.0	168200.0	235700.0	1986.0	3.0	3.0	0.0
1	16918.0	007 00 0 130.00	SINGLE FAMILY	1832 FOX CHASE DR, GOODLETTSVILLE	2014-06-10	366000.0	20140619-0053768	No	BONER, CHARLES & LESLIE	1832 FOX CHASE DR, GOODLETTSVILLE, TN	3.5	GENERAL SERVICES DISTRICT	50000.0	264100.0	319000.0	1998.0	3.0	3.0	2.0
2	54582.0	007 00 0 138.00	SINGLE FAMILY	1864 FOX CHASE DR, GOODLETTSVILLE	2016-09-26	435000.0	20160927-0101718	No	WILSON, JAMES E. & JOANNE	1864 FOX CHASE DR, GOODLETTSVILLE, TN	2.9	GENERAL SERVICES DISTRICT	50000.0	216200.0	298000.0	1987.0	4.0	3.0	0.0
3	43070.0	007 00 0 143.00	SINGLE FAMILY	1853 FOX CHASE DR, GOODLETTSVILLE	2016-01-29	255000.0	20160129-0008913	No	BAKER, JAY K. & SUSAN E.	1853 FOX CHASE DR, GOODLETTSVILLE, TN	2.6	GENERAL SERVICES DISTRICT	50000.0	147300.0	197300.0	1985.0	3.0	3.0	0.0
4	22714.0	007 00 0 149.00	SINGLE FAMILY	1829 FOX CHASE DR, GOODLETTSVILLE	2014-10-10	278000.0	20141015-0095255	No	POST, CHRISTOPHER M. & SAMANTHA C.	1829 FOX CHASE DR, GOODLETTSVILLE, TN	2.0	GENERAL SERVICES DISTRICT	50000.0	152300.0	202300.0	1984.0	4.0	3.0	0.0

	UniqueID	ParcelID	LandUse	PropertyAddress	SaleDate	SalePrice	LegalReference	SoldAsVacant	OwnerName	OwnerAddress	Acreage	TaxDistrict	LandValue	BuildingValue	TotalValue	YearBuilt	Bedrooms	FullBath	HalfBath
0	43076.0	025 07 0 031.00	SINGLE FAMILY	None	2016-01-15	179900.0	20160120-0005776	No	COSTNER, FRED & CAROLYN	410 ROSEHILL CT, GOODLETTSVILLE, TN	0.96	CITY OF GOODLETTSVILLE	30000.0	70000.0	100000.0	1964.0	3.0	1.0	0.0
1	39432.0	026 01 0 069.00	VACANT RESIDENTIAL LAND	None	2015-10-23	153000.0	20151028-0109602	No	SHACKLEFORD, MICHAEL C., JR.	141 TWO MILE PIKE, GOODLETTSVILLE, TN	0.17	CITY OF GOODLETTSVILLE	21100.0	121600.0	142700.0	2015.0	3.0	2.0	0.0
2	45290.0	026 05 0 017.00	SINGLE FAMILY	None	2016-03-29	155000.0	20160330-0029941	No	TRIPP, MARVIN S. & DEBORAH YOUNG	208 EAST AVE, GOODLETTSVILLE, TN	0.20	CITY OF GOODLETTSVILLE	21100.0	130200.0	151300.0	2008.0	3.0	2.0	0.0
3	53147.0	026 06 0A 038.00	RESIDENTIAL CONDO	None	2016-08-25	144900.0	20160831-0091567	No	None	None	NaN	None	NaN	NaN	NaN	NaN	NaN	NaN	NaN
4	43080.0	033 06 0 041.00	SINGLE FAMILY	None	2016-01-04	170000.0	20160107-0001526	No	FRANK, ZACHARY & NIKI	1129 CAMPBELL RD, GOODLETTSVILLE, TN	0.24	GENERAL SERVICES DISTRICT	35000.0	110500.0	145500.0	2000.0	3.0	2.0	0.0
5	45295.0	033 06 0A 002.00	SINGLE FAMILY	None	2016-03-29	210000.0	20160331-0030709	No	None	None	NaN	None	NaN	NaN	NaN	NaN	NaN	NaN	NaN
6	48731.0	033 15 0 123.00	SINGLE FAMILY	None	2016-05-05	199900.0	20160506-0045368	No	COLEMAN, AARON A. & CECIL, CORRIE J.	438 W CAMPBELL RD, GOODLETTSVILLE, TN	1.39	GENERAL SERVICES DISTRICT	45000.0	90300.0	135300.0	1954.0	2.0	1.0	0.0
7	36531.0	034 03 0 059.00	SINGLE FAMILY	None	2015-08-13	245000.0	20150819-0083759	No	DILICK, JOHN MARK & ANNETTE A.	2117 PAULA DR, MADISON, TN	1.01	GENERAL SERVICES DISTRICT	32000.0	170000.0	228300.0	1964.0	4.0	3.0	0.0
8	46919.0	034 07 0B 015.00	VACANT RESIDENTIAL LAND	None	2016-04-27	40000.0	20160304-0020905	Yes	None	None	NaN	None	NaN	NaN	NaN	NaN	NaN	NaN	NaN
9	44264.0	034 16 0A 004.00	VACANT RESIDENTIAL LAND	None	2016-02-04	130000.0	20160205-0011327	Yes	None	None	NaN	None	NaN	NaN	NaN	NaN	NaN	NaN	NaN
10	45298.0	041 03 0A 100.00	SINGLE FAMILY	None	2016-03-18	170000.0	20160322-0027048	No	None	None	NaN	None	NaN	NaN	NaN	NaN	NaN	NaN	NaN
11	40678.0	042 13 0 075.00	SINGLE FAMILY	None	2015-11-30	208000.0	20151209-0123831	No	SADOWSKY, ERIC C. & LISA D.	222 FOXBORO DR, MADISON, TN	1.01	GENERAL SERVICES DISTRICT	30000.0	100000.0	130000.0	1967.0	3.0	1.0	1.0
12	47293.0	043 04 0 014.00	SINGLE FAMILY	None	2016-04-29	190000.0	20160503-0043576	No	CRIPPS, WILLIE S. JR. & GINEA M.	112 HILLER DR, OLD HICKORY, TN	0.29	GENERAL SERVICES DISTRICT	17000.0	143700.0	173600.0	1956.0	4.0	2.0	1.0
13	22775.0	043 09 0 074.00	VACANT RESIDENTIAL LAND	None	2014-10-27	151000.0	20141028-0099094	Yes	BREWER HOLDINGS, LLC	213 B LOVELL ST, MADISON, TN	0.15	GENERAL SERVICES DISTRICT	18000.0	115600.0	133600.0	2015.0	3.0	2.0	0.0
14	45349.0	043 13 0 308.00	SINGLE FAMILY	None	2016-03-31	155000.0	20160405-0032445	No	JACKSON, ADAM L. & MOORE, MARY A.	224 HICKORY ST, MADISON, TN	0.16	GENERAL SERVICES DISTRICT	16000.0	65900.0	81900.0	1940.0	2.0	1.0	0.0
15	50927.0	044 05 0 135.00	SINGLE FAMILY	None	2016-06-15	160000.0	20160617-0061987	No	SMITH, WARREN & MARTHA	202 KEETON AVE, OLD HICKORY, TN	0.12	GENERAL SERVICES DISTRICT	19000.0	102700.0	121700.0	2015.0	4.0	2.0	0.0
16	3299.0	052 01 0 296.00	SINGLE FAMILY	None	2013-05-31	79370.0	20130620-0063114	No	KIRBY, WILLIAM E. JR	726 IDLEWILD DR, MADISON, TN	0.22	GENERAL SERVICES DISTRICT	16000.0	60800.0	76800.0	1957.0	2.0	1.0	0.0
17	43151.0	052 08 0A 320.00	SINGLE FAMILY	None	2016-01-15	150000.0	20160119-0004918	No	None	None	NaN	None	NaN	NaN	NaN	NaN	NaN	NaN	NaN
18	49886.0	092 06 0 273.00	SINGLE FAMILY	None	2016-06-21	186000.0	20160705-0068281	No	SOLAVA, RACHEL LYNN	2721 HERMAN ST, NASHVILLE, TN	0.17	URBAN SERVICES DISTRICT	9000.0	63200.0	72200.0	1987.0	2.0	1.0	1.0
19	27140.0	092 06 0 282.00	SINGLE FAMILY	None	2015-02-20	41500.0	20150224-0015900	No	PROSPERITAS PARTNERS LLC	815 31ST AVE N, NASHVILLE, TN	0.17	URBAN SERVICES DISTRICT	13000.0	49400.0	62400.0	1960.0	2.0	1.0	0.0
20	11478.0	092 13 0 322.00	SINGLE FAMILY	None	2014-01-17	269500.0	20140122-0006168	No	WIGGINS, MATHEW L. & GOOD, KENDRA A.	237 37TH AVE N, NASHVILLE, TN	0.34	URBAN SERVICES DISTRICT	91000.0	107100.0	198100.0	1945.0	2.0	1.0	0.0
21	32385.0	092 13 0 339.00	SINGLE FAMILY	None	2015-06-05	450000.0	20150618-0058311	No	JUMPER, NICOLE	311 35TH AVE N, NASHVILLE, TN	0.16	URBAN SERVICES DISTRICT	65000.0	365300.0	430300.0	2015.0	3.0	3.0	1.0
22	8126.0	093 08 0 054.00	SINGLE FAMILY	None	2013-09-20	25000.0	20130923-0099666	N	CRAWFORD, CHRISTOPHER C. & BOBBIE LEANN	700 GLENVIEW DR, NASHVILLE, TN	0.21	URBAN SERVICES DISTRICT	25000.0	236800.0	261800.0	2015.0	3.0	2.0	1.0
23	45774.0	107 13 0 107.00	SINGLE FAMILY	None	2016-03-28	169900.0	20160330-0030078	No	GROOMS, MICAH & CASEY	1205 THOMPSON PL, NASHVILLE, TN	0.39	URBAN SERVICES DISTRICT	17000.0	75600.0	96200.0	1949.0	3.0	1.0	0.0
24	14753.0	108 07 0A 026.00	RESIDENTIAL CONDO	None	2014-04-15	79900.0	20140416-0031777	No	None	None	NaN	None	NaN	NaN	NaN	NaN	NaN	NaN	NaN
25	15886.0	109 04 0A 080.00	VACANT RES LAND	None	2014-05-13	255590.0	20140514-0041276	No	None	None	NaN	None	NaN	NaN	NaN	NaN	NaN	NaN	NaN
26	24197.0	110 03 0A 061.00	SINGLE FAMILY	None	2014-11-19	269750.0	20141120-0106962	No	None	None	NaN	None	NaN	NaN	NaN	NaN	NaN	NaN	NaN
27	51930.0	113 14 0A 002.00	VACANT RESIDENTIAL LAND	None	2016-07-05	133000.0	20160707-0069375	Yes	None	None	NaN	None	NaN	NaN	NaN	NaN	NaN	NaN	NaN
28	51703.0	114 15 0A 030.00	RESIDENTIAL CONDO	None	2016-07-08	343235.0	20160713-0071757	No	None	None	NaN	None	NaN	NaN	NaN	NaN	NaN	NaN	NaN

	ParcelID	PropertyAddress	ParcelID	PropertyAddress	PropertyAddress
0	034 03 0 059.00	None	034 03 0 059.00	2117 PAULA DR, MADISON	2117 PAULA DR, MADISON
1	034 03 0 059.00	None	034 03 0 059.00	2117 PAULA DR, MADISON	2117 PAULA DR, MADISON
2	025 07 0 031.00	None	025 07 0 031.00	410 ROSEHILL CT, GOODLETTSVILLE	410 ROSEHILL CT, GOODLETTSVILLE
3	026 01 0 069.00	None	026 01 0 069.00	141 TWO MILE PIKE, GOODLETTSVILLE	141 TWO MILE PIKE, GOODLETTSVILLE
4	026 05 0 017.00	None	026 05 0 017.00	208 EAST AVE, GOODLETTSVILLE	208 EAST AVE, GOODLETTSVILLE
5	026 06 0A 038.00	None	026 06 0A 038.00	109 CANTON CT, GOODLETTSVILLE	109 CANTON CT, GOODLETTSVILLE
6	033 06 0 041.00	None	033 06 0 041.00	1129 CAMPBELL RD, GOODLETTSVILLE	1129 CAMPBELL RD, GOODLETTSVILLE
7	033 06 0A 002.00	None	033 06 0A 002.00	1116 CAMPBELL RD, GOODLETTSVILLE	1116 CAMPBELL RD, GOODLETTSVILLE
8	033 15 0 123.00	None	033 15 0 123.00	438 W CAMPBELL RD, GOODLETTSVILLE	438 W CAMPBELL RD, GOODLETTSVILLE
9	034 07 0B 015.00	None	034 07 0B 015.00	2524 VAL MARIE DR, MADISON	2524 VAL MARIE DR, MADISON
10	034 07 0B 015.00	None	034 07 0B 015.00	2524 VAL MARIE DR, MADISON	2524 VAL MARIE DR, MADISON
11	034 07 0B 015.00	None	034 07 0B 015.00	2524 VAL MARIE DR, MADISON	2524 VAL MARIE DR, MADISON
12	034 16 0A 004.00	None	034 16 0A 004.00	213 WARREN CT, OLD HICKORY	213 WARREN CT, OLD HICKORY
13	041 03 0A 100.00	None	041 03 0A 100.00	1289 GOODMORNING DR, NASHVILLE	1289 GOODMORNING DR, NASHVILLE
14	044 05 0 135.00	None	044 05 0 135.00	202 KEETON AVE, OLD HICKORY	202 KEETON AVE, OLD HICKORY
15	092 06 0 273.00	None	092 06 0 273.00	2721 HERMAN ST, NASHVILLE	2721 HERMAN ST, NASHVILLE
16	092 06 0 282.00	None	092 06 0 282.00	815 31ST AVE N, NASHVILLE	815 31ST AVE N, NASHVILLE
17	092 13 0 322.00	None	092 13 0 322.00	237 37TH AVE N, NASHVILLE	237 37TH AVE N, NASHVILLE
18	092 13 0 322.00	None	092 13 0 322.00	237 37TH AVE N, NASHVILLE	237 37TH AVE N, NASHVILLE
19	092 13 0 339.00	None	092 13 0 339.00	311 35TH AVE N, NASHVILLE	311 35TH AVE N, NASHVILLE
20	042 13 0 075.00	None	042 13 0 075.00	222 FOXBORO DR, MADISON	222 FOXBORO DR, MADISON
21	043 04 0 014.00	None	043 04 0 014.00	112 HILLER DR, OLD HICKORY	112 HILLER DR, OLD HICKORY
22	043 09 0 074.00	None	043 09 0 074.00	213 B LOVELL ST, MADISON	213 B LOVELL ST, MADISON
23	043 13 0 308.00	None	043 13 0 308.00	224 HICKORY ST, MADISON	224 HICKORY ST, MADISON
24	052 01 0 296.00	None	052 01 0 296.00	726 IDLEWILD DR, MADISON	726 IDLEWILD DR, MADISON
25	052 08 0A 320.00	None	052 08 0A 320.00	608 SANDY SPRING TRL, MADISON	608 SANDY SPRING TRL, MADISON
26	093 08 0 054.00	None	093 08 0 054.00	700 GLENVIEW DR, NASHVILLE	700 GLENVIEW DR, NASHVILLE
27	093 08 0 054.00	None	093 08 0 054.00	700 GLENVIEW DR, NASHVILLE	700 GLENVIEW DR, NASHVILLE
28	107 13 0 107.00	None	107 13 0 107.00	1205 THOMPSON PL, NASHVILLE	1205 THOMPSON PL, NASHVILLE
29	108 07 0A 026.00	None	108 07 0A 026.00	908 PATIO DR, NASHVILLE	908 PATIO DR, NASHVILLE
30	108 07 0A 026.00	None	108 07 0A 026.00	908 PATIO DR, NASHVILLE	908 PATIO DR, NASHVILLE
31	109 04 0A 080.00	None	109 04 0A 080.00	2537 JANALYN TRCE, HERMITAGE	2537 JANALYN TRCE, HERMITAGE
32	110 03 0A 061.00	None	110 03 0A 061.00	2704 ALVIN SPERRY PASS, MOUNT JULIET	2704 ALVIN SPERRY PASS, MOUNT JULIET
33	113 14 0A 002.00	None	113 14 0A 002.00	7601 CHIPMUNK LN, NASHVILLE	7601 CHIPMUNK LN, NASHVILLE
34	114 15 0A 030.00	None	114 15 0A 030.00	109 CEDAR PLACE BND, NASHVILLE	109 CEDAR PLACE BND, NASHVILLE

	PropertyAddress
0	1808 FOX CHASE DR, GOODLETTSVILLE
1	1832 FOX CHASE DR, GOODLETTSVILLE
2	1864 FOX CHASE DR, GOODLETTSVILLE
3	1853 FOX CHASE DR, GOODLETTSVILLE
4	1829 FOX CHASE DR, GOODLETTSVILLE

	PropertyAddressStreet	PropertyAddressCity
0	1808 FOX CHASE DR	GOODLETTSVILLE
1	1832 FOX CHASE DR	GOODLETTSVILLE
2	1864 FOX CHASE DR	GOODLETTSVILLE
3	1853 FOX CHASE DR	GOODLETTSVILLE
4	1829 FOX CHASE DR	GOODLETTSVILLE

Housing Data Cleaning Using Microsoft SQL Server

Populating the PropertyAddress column

Breaking down the address into separate columns

PropertyAddress

OwnerAddress

Replacing 'Y' and 'N' values in SoldAsVacant column to 'Yes' and 'No' respectively for uniformity

Removing duplicate data

	SoldAsVacant	Count_SoldAsVacant
0	No	51403
1	Yes	4623
2	N	399
3	Y	52

	SoldAsVacant
0	N	No
1	N	No
2	N	No
3	N	No
4	N	No
...	...	...
446	N	No
447	N	No
448	N	No
449	N	No
450	N	No